create table SANBAY
(
	MASANBAY  NVARCHAR(3) NOT NULL CONSTRAINT PK_SANBAY PRIMARY KEY(MASANBAY),
	TENSANBAY NVARCHAR(15) NOT NULL
)
CREATE TABLE TUYENBAY
(
	MATUYENBAY NVARCHAR(10) NOT  NULL CONSTRAINT PK_MATUYENBAY PRIMARY KEY(MATUYENBAY),
	SANBAYDI NVARCHAR(3) NOT  NULL,
	SANBAYDEN NVARCHAR(3) NOT NULL
)

CREATE TABLE CHUYENBAY
(
	MACHUYENBAY NVARCHAR(4) NOT NULL,
	MATUYENBAY NVARCHAR(10) NOT NULL,
	NGAYGIO DATETIME NOT NULL,
	KHOIHANH DATETIME NULL,
	DEN DATETIME NULL,
	THOIGIANBAY DATETIME NULL,
	SLGHEHANG1 INT CONSTRAINT CK_HV1 CHECK(SLGHEHANG1 >=0 AND SLGHEHANG1 <=100),
	SLGHEHANG2 INT CONSTRAINT CK_HV2 CHECK(SLGHEHANG2 >=0 AND SLGHEHANG2 <=100),
	CONSTRAINT PK_MACHUYENBAY PRIMARY KEY(MACHUYENBAY),
	CONSTRAINT FK_MATUYENBAY FOREIGN KEY(MATUYENBAY) REFERENCES TUYENBAY(MATUYENBAY)
)

 

CREATE TABLE HANGVE
(
	MAHANGVE NVARCHAR(4)NOT NULL CONSTRAINT PK_HANGVE PRIMARY KEY(MAHANGVE),
	TENHANGVE NVARCHAR(15) NOT NULL,
	CONSTRAINT CK_MAHANGVE CHECK(MAHANGVE IN('HV01', 'HV02'))

)

CREATE TABLE KHACHHANG
(	MAKH INT IDENTITY(1,1) NOT NULL,
	CMND nvarchar(10) NOT NULL CONSTRAINT PK_CMND PRIMARY KEY(CMND),
	TENKH NVARCHAR(50) NOT NULL,
	DIENTHOAI NVARCHAR(12) NULL

)

CREATE TABLE PHIEUDATCHO
(
	MAPHIEU INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_MAPHIEU PRIMARY KEY(MAPHIEU) ,
	CMND nvarchar(10) NOT NULL CONSTRAINT FK_CMND FOREIGN KEY(CMND) REFERENCES KHACHHANG(CMND),
	MACHUYENBAY NVARCHAR(4) NOT NULL CONSTRAINT FK_CB_PDC FOREIGN KEY(MACHUYENBAY) REFERENCES CHUYENBAY(MACHUYENBAY),
	MAHANGVE NVARCHAR(4)NOT NULL CONSTRAINT FK_HANGVE_PDC FOREIGN KEY(MAHANGVE) REFERENCES HANGVE(MAHANGVE),
	NGAYDAT DATETIME NOT NULL,
	SOGHE INT NOT NULL
)

CREATE TABLE DONGIA
(
	MADG INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_MADG PRIMARY KEY(MADG),
	MATUYENBAY NVARCHAR(10) NOT  NULL CONSTRAINT FK_TUYENBAY_DG FOREIGN KEY(MATUYENBAY) REFERENCES TUYENBAY(MATUYENBAY),
	MAHANGVE NVARCHAR(4)NOT NULL CONSTRAINT FK_HV_DG FOREIGN KEY(MAHANGVE) REFERENCES HANGVE(MAHANGVE),
	USD NUMERIC(18,0) NULL,
	VND NUMERIC(18,0)NULL
)

CREATE TABLE NHANVIEN
(
	MANV NVARCHAR(5) NOT NULL CONSTRAINT PK_MANV PRIMARY KEY(MANV),
	TENNV NVARCHAR(30) NOT NULL,
)

CREATE TABLE TINHTRANGVE
(
	MA INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_TTV PRIMARY KEY(MA),
	MACHUYENBAY NVARCHAR(4) NOT NULL CONSTRAINT FK_CB_TTV FOREIGN KEY(MACHUYENBAY) REFERENCES CHUYENBAY(MACHUYENBAY),
	MAHANGVE NVARCHAR(4)NOT NULL CONSTRAINT FK_HV_TTV FOREIGN KEY(MAHANGVE)REFERENCES HANGVE(MAHANGVE),
	SOGHETRONG INT NOT NULL,
	SOGHEDAT INT NOT NULL
)

CREATE TABLE CHITIETCHUYENBAY
(
	MACT_CB INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_MACT_CB PRIMARY KEY(MACT_CB),
	MACHUYENBAY NVARCHAR(4) NOT NULL CONSTRAINT FK_CB_CTCB FOREIGN KEY(MACHUYENBAY) REFERENCES CHUYENBAY(MACHUYENBAY),
	SB_TRUNGGIAN NVARCHAR(3) NULL,
	TGDUNG DATETIME NULL,
	GHICHU NVARCHAR(50)NULL
	
)

CREATE TABLE THAMSO
(
	TGBayToiThieu int not null,
	SoSanBayTrungGianToiDa int not null,
	TGDungToiThieu int not null,
	TGDungToiDa int not null,
	TGChamNhatDatVe int not null,
	TGChamNhatHuyDatVe int not null

)



CREATE TABLE PHANQUYEN
(
	MAQUYEN  NVARCHAR(4)NOT NULL CONSTRAINT PK_MAQUYEN PRIMARY KEY(MAQUYEN),
	TENQUYEN NVARCHAR(10) NOT NULL 
)

CREATE TABLE USERS
(
	USERNAME NVARCHAR(25) NOT NULL CONSTRAINT PK_USERNAME PRIMARY KEY(USERNAME),
	PASSWORD NVARCHAR(10) NOT NULL,
	HOTEN NVARCHAR(50) NOT NULL,
	MAQUYEN NVARCHAR (4)NOT NULL CONSTRAINT FK_PHANQUYEN_USERS FOREIGN KEY(MAQUYEN) REFERENCES PHANQUYEN(MAQUYEN)
	
)


/*CREATE PROCEDURE [dbo].[delete_sanbay]
 @masb nvarchar(3)
 AS
set nocount on
DELETE  FROM Sanbay  WHERE MaSanbay = @masb*/

CREATE PROCEDURE UpdateSB_Proc
(	@masb as nvarchar(3),
	@tensb	as nvarchar(15)
)
	
AS
update SANBAY set
TENSANBAY= @tensb
where MASANBAY =@masb
GO




